package com.clubupc.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.Map;
import java.util.Set;

import com.clubupc.exception.DAOException;
import com.clubupc.modelo.BaseBean;
import com.clubupc.modelo.ProxyBeans;
import com.clubupc.util.ConexionBD;

public class BaseDAO {

	private static String[] masterTablesNames = { "Material", "Juego", "Club", "Servicio", "TipoUsuario", "Sexo", "TipoDocumento", "EstadoDiaCampo",
			"EstadoPeticionOferta", "Oferta" };
	private static Map<String, LinkedList<BaseBean>> masterTables = null;

	public static void loadMasterTables() {
		masterTables = new HashMap<String, LinkedList<BaseBean>>();
		for (int i = 0; i < masterTablesNames.length; i++) {
			masterTables.put(masterTablesNames[i], getMasterTable(masterTablesNames[i]));
		}
	}

	public static LinkedList<BaseBean> getMasterTable(String table) {
		LinkedList<BaseBean> tabla = null;
		try {
			BaseDAO bd = new BaseDAO();
			tabla = bd.selectByParams(null, table);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		return tabla;
	}
	
	public static Map<String, LinkedList<BaseBean>> getMasterTables() {
		return masterTables;
	}
	
	protected void cerrarConexion(Connection con) throws RuntimeException {
		try {
			if (con != null && !con.isClosed()) {
				con.close();
			}
		} catch (SQLException se) {
			System.err.println("Error: cerrarConexion: " + se);
		}
	}

	protected void cerrarResultSet(ResultSet rs) throws RuntimeException {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException se) {
			System.err.println("Error: cerrarResultSet: " + se);
		}
	}

	protected void cerrarStatement(PreparedStatement stmt) throws RuntimeException {
		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException se) {
			System.err.println("Error: cerrarStatement: " + se);
		}
	}

	protected void cerrarCallable(CallableStatement callstmt) throws RuntimeException {
		try {
			if (callstmt != null) {
				callstmt.close();
			}
		} catch (SQLException se) {
			System.err.println("Error: cerrarCallable: " + se);
		}
	}

	public BaseBean insertByBaseBean(BaseBean bean) throws DAOException {
		String table = bean.getTable();
		if (table == null) {
			throw new RuntimeException("No existe el campo tabla, el bean debe obtenerlo de la clase ProxyBeans");
		}
		Map<String, String> config = bean.getConfig();
		String insert = geMapAsInsert(config, bean.getTable());
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(insert);
			String[] keys = config.keySet().toArray(new String[] {});
			for (int i = 0, x = 1; i < keys.length; x++, i++) {
				String param = keys[i];
				Object dato = bean.get(param);
				if (param.equalsIgnoreCase("activo") || param.equalsIgnoreCase(("id" + table))) {
					x--;
					continue;
				}
				stmt.setObject(x, dato);
			}
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			int id = 0;
			String query = "select last_insert_id()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				id = rs.getInt(1);
			}
			bean.putId(("id" + table), id);
			bean.put("activo", true);
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return bean;
	}

	public LinkedList<BaseBean> selectByParams(Map<String, Object> params, String table) throws DAOException {
		BaseBean baseClass = ProxyBeans.getBaseBean(table);
		if (baseClass == null) {
			throw new RuntimeException("No existe configuracion para la tabla:'" + table + "' en el archivo 'tables.properties'");
		}
		return selectByQueryAndBaseClass(geMapAsQuery(params, table), baseClass);
	}

	public LinkedList<BaseBean> selectByQuery(String query) throws DAOException {
		BaseBean baseClass = new BaseBean();
		return selectByQueryAndBaseClass(query, baseClass);
	}

	private LinkedList<BaseBean> selectByQueryAndBaseClass(String query, BaseBean baseClass) throws DAOException {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		LinkedList<BaseBean> linked = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int sizeColsRsmd = rsmd.getColumnCount();
			LinkedHashMap<String, String> colsRsmd = new LinkedHashMap<String, String>();
			for (int a = 1; a <= sizeColsRsmd; a++) {
				String type = rsmd.getColumnClassName(a);
				colsRsmd.put(rsmd.getColumnName(a), type);
			}
			String[] colsRsmdKeys = colsRsmd.keySet().toArray(new String[] {});
			if (baseClass.getTable() == null && baseClass.getConfig() == null) {
				baseClass.setConfig(colsRsmd);
			}
			linked = new LinkedList<BaseBean>();
			while (rs.next()) {
				BaseBean bean = baseClass.newInstance();
				for (int a = 0; a < colsRsmdKeys.length; a++) {
					String colRsmdName = colsRsmdKeys[a];
					String colBeanType = bean.getPropType(colRsmdName);
					if (colBeanType == null) {
						throw new RuntimeException("No existe la propiedad:'" + colRsmdName
								+ "' en el BaseBean, revise el archivo 'tables.properties'");
					}
					Object dato = rs.getObject(colRsmdName);
					if (!rs.wasNull()) {
						if (colBeanType.equalsIgnoreCase("com.clubupc.modelo.BaseBean")) {
							Map<String, Object> pars = new HashMap<String, Object>();
							pars.put(colRsmdName, dato);
							pars.put("activo", true);
							LinkedList<BaseBean> subLista = selectByParams(pars, colRsmdName.substring(2));
							dato = subLista.get(0);
						}
					}
					bean.put(colRsmdName, dato);
				}
				linked.add(bean);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return linked;
	}

	private String geMapAsQuery(Map<String, Object> params, String table) {
		StringBuilder query = new StringBuilder();
		query.append("select * from ").append(table);
		if (params != null && !params.isEmpty()) {
			String[] keys = params.keySet().toArray(new String[] {});
			int size = keys.length;
			query.append(" where ");
			for (int i = 0; i < size; i++) {
				String key = keys[i];
				query.append(key);
				query.append("=");
				Object param = params.get(key);
				if(param instanceof java.lang.String){
					query.append("'");
					query.append(param);
					query.append("'");
				}else{
					query.append(param);
				}
				query.append(i < size - 1 ? " and " : "");
			}
			query.append(params.containsKey("activo") ? "" : " and activo=true");
		}
		return query.toString();
	}

	private String geMapAsInsert(Map<String, String> params, String table) {
		StringBuilder query = new StringBuilder();
		query.append("insert into ").append(table).append(" (");
		if (params != null && !params.isEmpty()) {
			Set<String> keySet = params.keySet();
			String[] keys = keySet.toArray(new String[] {});
			LinkedList<String> keysList = new LinkedList<String>();
			for (int i = 0; i < keys.length; i++) {
				String key = keys[i];
				if (key.equalsIgnoreCase("activo") || key.equalsIgnoreCase("id" + table)) {
					continue;
				}
				keysList.add(key);
			}
			int size = keysList.size();
			StringBuilder comas = new StringBuilder();
			for (int i = 0; i < keysList.size(); i++) {
				query.append(keysList.get(i));
				comas.append("?");
				if (i < size - 1) {
					query.append(", ");
					comas.append(", ");
				}
			}
			query.append(") values (").append(comas.toString()).append(")");
		}
		return query.toString();
	}
}
